const pool = require('../config/database');

class AncillaryExamModel {
  /**
   * 创建辅助检查记录
   * @param {Object} examData - 检查数据
   * @returns {Promise<Object>} 创建的检查记录
   */
  static async create(examData) {
    const connection = await pool.getConnection();
    
    try {
      await connection.beginTransaction();
      
      const fields = Object.keys(examData);
      const values = Object.values(examData);
      const placeholders = fields.map(() => '?').join(', ');
      
      const sql = `
        INSERT INTO jktj_ancillary_exam (${fields.join(', ')})
        VALUES (${placeholders})
      `;
      
      const [result] = await connection.execute(sql, values);
      
      await connection.commit();
      
      return {
        id: result.insertId,
        ...examData
      };
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  /**
   * 根据体检ID获取辅助检查记录
   * @param {number} tjId - 体检ID
   * @returns {Promise<Object>} 检查记录
   */
  static async findByTjId(tjId) {
    const sql = 'SELECT * FROM jktj_ancillary_exam WHERE tj_id = ?';
    const [rows] = await pool.execute(sql, [tjId]);
    return rows[0] || null;
  }

  /**
   * 根据ID获取辅助检查记录
   * @param {number} id - 检查记录ID
   * @returns {Promise<Object>} 检查记录
   */
  static async findById(id) {
    const sql = 'SELECT * FROM jktj_ancillary_exam WHERE id = ?';
    const [rows] = await pool.execute(sql, [id]);
    return rows[0] || null;
  }

  /**
   * 更新辅助检查记录
   * @param {number} tjId - 体检ID
   * @param {Object} updateData - 更新数据
   * @returns {Promise<boolean>} 更新结果
   */
  static async update(tjId, updateData) {
    const fields = Object.keys(updateData);
    const values = Object.values(updateData);
    const setClause = fields.map(field => `${field} = ?`).join(', ');
    
    const sql = `UPDATE jktj_ancillary_exam SET ${setClause} WHERE tj_id = ?`;
    const [result] = await pool.execute(sql, [...values, tjId]);
    
    return result.affectedRows > 0;
  }

  /**
   * 删除辅助检查记录
   * @param {number} tjId - 体检ID
   * @returns {Promise<boolean>} 删除结果
   */
  static async delete(tjId) {
    const sql = 'DELETE FROM jktj_ancillary_exam WHERE tj_id = ?';
    const [result] = await pool.execute(sql, [tjId]);
    return result.affectedRows > 0;
  }

  /**
   * 批量获取多个体检的辅助检查记录
   * @param {Array<number>} tjIds - 体检ID数组
   * @returns {Promise<Array>} 检查记录数组
   */
  static async findByTjIds(tjIds) {
    if (!tjIds || tjIds.length === 0) {
      return [];
    }
    
    const placeholders = tjIds.map(() => '?').join(', ');
    const sql = `SELECT * FROM jktj_ancillary_exam WHERE tj_id IN (${placeholders})`;
    const [rows] = await pool.execute(sql, tjIds);
    return rows;
  }

  /**
   * 检查血常规是否异常
   * @param {Object} examData - 检查数据
   * @returns {Object} 异常检查结果
   */
  static checkBloodRoutineAbnormalities(examData) {
    const abnormalities = [];
    
    // 血红蛋白正常范围：男性120-160g/L，女性110-150g/L
    if (examData.xhdb) {
      const hb = parseFloat(examData.xhdb);
      if (hb < 110 || hb > 160) {
        abnormalities.push({
          item: '血红蛋白',
          value: examData.xhdb,
          status: hb < 110 ? '偏低' : '偏高'
        });
      }
    }
    
    // 白细胞正常范围：4-10×10^9/L
    if (examData.bxb) {
      const wbc = parseFloat(examData.bxb);
      if (wbc < 4 || wbc > 10) {
        abnormalities.push({
          item: '白细胞',
          value: examData.bxb,
          status: wbc < 4 ? '偏低' : '偏高'
        });
      }
    }
    
    // 血小板正常范围：100-300×10^9/L
    if (examData.xxb) {
      const plt = parseFloat(examData.xxb);
      if (plt < 100 || plt > 300) {
        abnormalities.push({
          item: '血小板',
          value: examData.xxb,
          status: plt < 100 ? '偏低' : '偏高'
        });
      }
    }
    
    return abnormalities;
  }

  /**
   * 检查血糖是否异常
   * @param {Object} examData - 检查数据
   * @returns {Object} 异常检查结果
   */
  static checkBloodGlucoseAbnormalities(examData) {
    const abnormalities = [];
    
    // 空腹血糖正常范围：3.9-6.1mmol/L
    if (examData.kfxt) {
      const glucose = parseFloat(examData.kfxt);
      if (glucose < 3.9 || glucose > 6.1) {
        abnormalities.push({
          item: '空腹血糖',
          value: examData.kfxt,
          status: glucose < 3.9 ? '偏低' : '偏高'
        });
      }
    }
    
    return abnormalities;
  }
}

module.exports = AncillaryExamModel;